﻿using LinkSql;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using hospital_registration_system.Entity;

namespace hospital_registration_system.DAL
{
    public class IllnessDao
    {
        MySqlDataAdapter mySqlDataAdapter;

       
        /// <summary>
        /// 通过id查询
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Illness select(int id)
        {
            Illness illness;
            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            try
            {
                connection.Open();
                string sql = "select * from illness where id = " + id;
                mySqlDataAdapter = new MySqlDataAdapter(sql, connection);
                DataTable dataTable = new DataTable();
                mySqlDataAdapter.Fill(dataTable);
                if (dataTable.Rows.Count == 0)
                {
                    return null;
                }
                else
                {
                    illness = new Illness(
                    int.Parse(dataTable.Rows[0]["id"].ToString()),
                    dataTable.Rows[0]["illness_name"].ToString(),
                    dataTable.Rows[0]["created_by"].ToString(),
                    (DateTime)(dataTable.Rows[0]["created_time"]),
                    dataTable.Rows[0]["updated_by"].ToString(),
                    (DateTime)(dataTable.Rows[0]["updated_time"]),
                    int.Parse(dataTable.Rows[0]["state"].ToString())
                                    );

                }
            }
            catch (MySqlException)
            {
                return null;
            }
            finally
            {

                connection.Close();
            }

            return illness;
        }

        /// <summary>
        /// 通过illness对象插入信息
        /// </summary>
        /// <param name="illness"></param>
        /// <returns></returns>
        public bool insert(Illness illness)
        {
            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            try
            {
                string sql = "insert into illness(illness_name,created_by,created_time,updated_by,updated_time,state) values("
                    + "'" + illness.Illness_name + "'" + ","
                    + "'" + illness.CREATED_BY1 + "'" + ","
                    + "'" + illness.CREATED_TIME1 + "'" + ","
                    + "'" + illness.UPDATED_BY1 + "'" + ","
                    + "'" + illness.UPDATED_TIME1 + "'" + ","
                    + "'" + illness.State + "'"
                    + ")";
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException)
            {

                return false;
            }
            finally
            {
                connection.Close();
            }

            return true;
        }

        /// <summary>
        /// 通过id删除信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool delete(int id)
        {
            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            string sql = "delete from illness where id = " + id ;
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException)
            {
                return false;
            }
            finally
            {
                connection.Close();
            }
            return true;
        }

        /// <summary>
        /// 通过illness对象修改信息
        /// </summary>
        /// <param name="illness"></param>
        /// <returns></returns>
        public bool update(Illness illness)
        {
            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            string sql = "update illness set "
                + "illness_name='" + illness.Illness_name + "'," +
                "created_by='" + illness.CREATED_BY1 + "'," +
                "created_time='" + illness.CREATED_TIME1 + "'," +
                "updated_by='" + illness.UPDATED_BY1 + "'," +
                "updated_time='" + illness.UPDATED_TIME1 + "'," +
                "state='" + illness.State + "' " +
                "where id = " + illness.Id
                ;
            try
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                cmd.ExecuteNonQuery();
            }
            catch (MySqlException)
            {
                return false;
            }
            finally
            {
                connection.Close();
            }
            return true;
        }

        /// <summary>
        /// 查询所有
        /// </summary>
        /// <returns></returns>
        public List<Illness> selectAll()
        {
            List<Illness> illnesses= new List<Illness>();

            MySqlConnection connection = MySQLUtils.GetMySQLConnection();
            try
            {
                connection.Open();
                string sql = "select * from illness";
                mySqlDataAdapter = new MySqlDataAdapter(sql, connection);
                DataTable dataTable = new DataTable();
                mySqlDataAdapter.Fill(dataTable);
                if (dataTable.Rows.Count == 0)
                {
                    return null;
                }
                else
                {
                    foreach (DataRow row in dataTable.Rows)
                    {
                        Illness illness = new Illness(
                            int.Parse(row["id"].ToString()),
                            row["illness_name"].ToString(),
                            row["created_by"].ToString(),
                            (DateTime)(row["created_time"]),
                            row["updated_by"].ToString(),
                            (DateTime)(row["updated_time"]),
                            int.Parse(row["state"].ToString())
                        );
                        illnesses.Add(illness);
                    }
                }
            }
            catch (MySqlException)
            {
                return null;
            }
            finally
            {

                connection.Close();
            }

            return illnesses;
        }
    }
}
